﻿/*----------------------------------------------------------------
// Copyright (C) 2017 佰林格都信息科技有限公司 版权所有。 
//
// 文件名：DMember
// 文件功能描述：成员 提供处理各种管理[TB_Member]表的数据访问方法
//
// 创建标识：jinchuanliang 2018-07-14
//
// 修改标识：
// 修改描述：
//----------------------------------------------------------------*/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using BLGD.DataAccess;
using BLGD.Entity;
using FamilyTree.Entity;
using System.Text;

namespace FamilyTree.DAL
{
    /// <summary>
    /// 提供处理各种管理成员  [TB_Member]表的数据访问方法
    /// </summary>
	/// <author>jinchuanliang 2018-07-14</author>
    public class DMember
    {
        /// <summary>
        /// CRUD实体操作对象
        /// </summary>
        internal readonly static EntityCRUD<MemberEntity> Crud = new EntityCRUD<MemberEntity>();

        /// <summary>
        /// 是否已存在此对应数据
        /// </summary>
        /// <param name="strVal"></param> 
        /// <returns></returns>
        public bool IsExists(string strVal)
        {
            string sql = "select MemberId from TB_Member with(nolock) where **=@strVal ";
            List<DbParameter> plist = new List<DbParameter>();
            plist.Add(new SqlParameter("@strVal", strVal));
            object obj = SqlHelper.ExecuteValue(sql, CommandType.Text, plist);

            if (obj != null && Convert.ToInt32(obj) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        /// <summary>
        /// 添加tb_member数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public int Insert(MemberEntity model)
        {

            string sql = @"
            INSERT INTO TB_Member(MemberName,Sex,CardNumber,Phone,Birthday,FamilyInfoId,Ranking,PicUrl,Profile,DieTime,ParentId,Remarks)
            VALUES (@memberid,@membername,@sex,@cardnumber,@phone,@birthday,@familyinfoid,@ranking,@picurl,@profile,@dietime,@parentid,@remarks)
            select @@identity
            ";
            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@membername", model.MemberName));
            p.Add(new SqlParameter("@sex", model.Sex));
            p.Add(new SqlParameter("@cardnumber", model.CardNumber));
            p.Add(new SqlParameter("@phone", model.Phone));
            p.Add(new SqlParameter("@birthday", model.Birthday));
            p.Add(new SqlParameter("@familyinfoid", model.FamilyInfoId));
            p.Add(new SqlParameter("@ranking", model.Ranking));
            p.Add(new SqlParameter("@picurl", model.PicUrl));
            p.Add(new SqlParameter("@profile", model.Profile));
            p.Add(new SqlParameter("@dietime", model.DieTime));
            p.Add(new SqlParameter("@parentid", model.ParentId));
            p.Add(new SqlParameter("@remarks", model.Remarks));

            object i = SqlHelper.ExecuteValue(sql, CommandType.Text, p);
            return Convert.ToInt32(i);
        }
 
        /// <summary>
        /// 更新表信息tb_member数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool Update(MemberEntity model)
        {
            string sql = @"
            UPDATE TB_Member SET MemberName=@membername,Sex=@sex,CardNumber=@cardnumber,Phone=@phone,Birthday=@birthday,FamilyInfoId=@familyinfoid,Ranking=@ranking,PicUrl=@picurl,Profile=@profile,DieTime=@dietime,ParentId=@parentid,Remarks=@remarks          
             where MemberId=@memberid";
            List<SqlParameter> p = new List<SqlParameter>();
            p.Add(new SqlParameter("@memberid", model.MemberId));
            p.Add(new SqlParameter("@membername", model.MemberName));
            p.Add(new SqlParameter("@sex", model.Sex));
            p.Add(new SqlParameter("@cardnumber", model.CardNumber));
            p.Add(new SqlParameter("@phone", model.Phone));
            p.Add(new SqlParameter("@birthday", model.Birthday));
            p.Add(new SqlParameter("@familyinfoid", model.FamilyInfoId));
            p.Add(new SqlParameter("@ranking", model.Ranking));
            p.Add(new SqlParameter("@picurl", model.PicUrl));
            p.Add(new SqlParameter("@profile", model.Profile));
            p.Add(new SqlParameter("@dietime", model.DieTime));
            p.Add(new SqlParameter("@isdelete", model.IsDelete));
            p.Add(new SqlParameter("@parentid", model.ParentId));
            p.Add(new SqlParameter("@remarks", model.Remarks));

            return SqlHelper.ExecuteSql(sql, p) > 0;

        }

        /// <summary>
        /// 更新表信息tb_member数据 纯SQL操作
        /// </summary>
        /// <param name="model">数据实体</param> 
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool Update(string fileVal, string file, string strWhere)
        {
            string sql = @"
            UPDATE TB_Member SET " + file + " = " + fileVal + " where " + strWhere;

            return SqlHelper.ExecuteSql(sql, null) > 0;
        }

        /// <summary>
        /// 标记删除tb_member数据
        /// </summary>
        /// <param name="memberId">成员ID </param>
        /// <param name="userId">操作人ID</param>
        /// 创建人：jinchuanliang 时间：2018-07-14
        /// <returns></returns>
        public bool SignDelete(int memberId)
        {
            string sql = "update TB_Member set isdelete=1  where MemberId=@memberid ";

            List<SqlParameter> plist = new List<SqlParameter>(); 
            plist.Add(new SqlParameter("@memberid", memberId));

            int i = SqlHelper.ExecuteSql(sql, plist);

            return i > 0 ? true : false;
        }


        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="exid"></param>
        /// <returns></returns>
        public MemberEntity GetModel(int memberId)
        {
            const string sql = @"
            SELECT A.MemberId,A.MemberName,A.Sex,A.CardNumber,A.Phone,A.Birthday,A.FamilyInfoId,A.Ranking,A.PicUrl,A.Profile,A.DieTime,A.ParentId,A.Remarks  
            FROM TB_Member A with(nolock) where MemberId=@MemberId  and isdelete=0 ";

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@MemberId", memberId));

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemberEntity>(dt)[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="familyInfoId"></param>
        /// <returns></returns>
        public List<MemberEntity> GetModels(int familyInfoId)
        {
            const string sql = @"
            SELECT A.MemberId,A.MemberName,A.Sex,A.CardNumber,A.Phone,A.Birthday,A.FamilyInfoId,A.Ranking,A.PicUrl,A.Profile,A.DieTime,A.ParentId,A.Remarks 
            FROM TB_Member A with(nolock) where FamilyInfoId=@familyInfoId  and isdelete=0 ";

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@familyInfoId", familyInfoId));

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemberEntity>(dt);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="familyInfoId"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public List<MemberEntity> GetModels(int familyInfoId, int parentId)
        {
            const string sql = @"
            SELECT A.MemberId,A.MemberName,A.Sex,A.CardNumber,A.Phone,A.Birthday,A.FamilyInfoId,A.Ranking,A.PicUrl,A.Profile,A.DieTime,A.ParentId,A.Remarks 
            FROM TB_Member A with(nolock) where FamilyInfoId=@familyInfoId  and parentId=@parentId  and isdelete=0 ";

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@familyInfoId", familyInfoId));
            p.Add(new SqlParameter("@parentId", parentId));

            DataTable dt = SqlHelper.ExecuteDt(sql, CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemberEntity>(dt);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        ///  获取ModelList
        /// </summary> 
        /// <param name="familyInfoId"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public List<MemberEntity> GetModels(int familyInfoId,int pageIndex, int pageSize)
        {
            StringBuilder sb = new StringBuilder(@"
            SELECT top (@pageSize) A.MemberId,A.MemberName,A.Sex,A.CardNumber,A.Phone,A.Birthday,A.FamilyInfoId,A.Ranking,A.PicUrl,A.Profile,A.DieTime,A.ParentId,A.Remarks 
            FROM TB_Member A with(nolock) where FamilyInfoId=@familyInfoId and isdelete=0 ");
            if (pageIndex > 1)
            {
                sb.Append(@" and A.MemberId <(select min(MemberId) as mid from (select top ((@pageIndex-1)*@pageSize) MemberId from TB_Member  where FamilyInfoId=@familyInfoId and isdelete=0 order by MemberId desc) t)");
            }
            sb.Append(" order by A.MemberId desc ");

            List<DbParameter> p = new List<DbParameter>();
            p.Add(new SqlParameter("@familyInfoId", familyInfoId));
            p.Add(new SqlParameter("@pageIndex", pageIndex));
            p.Add(new SqlParameter("@pageSize", pageSize));

            DataTable dt = SqlHelper.ExecuteDt(sb.ToString(), CommandType.Text, p);

            if (dt != null && dt.Rows.Count > 0)
            {
                return EntityAux.ConvertDataSetToList<MemberEntity>(dt);
            }
            else
            {
                return null;
            }
        }
    }
}

